/*
 * 
 * Copyright 2011 visual-analysis-of-cancer.
 * This file is part of visual-analysis-of-cancer.
 * 
 * visual-analysis-of-cancer is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * visual-analysis-of-cancer is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with kOSMik.  If not, see <http://www.gnu.org/licenses/>.
 *  
 * visual-analysis-of-cancer:
 * An tool to analye the WHO and GLOBOCAN cancer data.
 * http://code.google.com/p/visual-analysis-of-cancer/
 * 
 * 
 * 
 * Authors: Erik Sniegula, Johannes Dahlke
 * 
 * Module:  Programming in Processing for Visual Analytics
 * Group:   20
 * 
 * Created: Aug. 2011
 * Version: 1.0
 *
 */

package de.fuberlin.processing.who.cancer.data;

import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Properties;
import java.util.Vector;
import de.fuberlin.processing.who.cancer.data.structs.StructAirPollutionExposureOfLocation;
import de.fuberlin.processing.who.cancer.data.structs.StructCancerAgeSpreadingOfLocation;
import de.fuberlin.processing.who.cancer.data.structs.StructCancerCauseAirPollutionOfLocation;
import de.fuberlin.processing.who.cancer.data.structs.StructCancerCauseUvRadiationOfLocation;
import de.fuberlin.processing.who.cancer.data.structs.StructCancerIncidenceOfLocation;
import de.fuberlin.processing.who.cancer.data.structs.StructCancerMortalityOfLocation;
import de.fuberlin.processing.who.cancer.data.structs.StructCancerSexSpreadingOfLocation;
import de.fuberlin.processing.who.cancer.data.structs.StructHealthFinancingOfLocation;
import de.fuberlin.processing.who.cancer.data.structs.StructLifeExpectancyAndHealthFinancingAndCancerDataForLocation;
import de.fuberlin.processing.who.cancer.data.structs.StructLifeExpectancyOfLocation;
import de.fuberlin.processing.who.cancer.data.structs.StructLocationData;
import de.fuberlin.processing.who.cancer.data.structs.StructUVRadiationExposureOfLocation;
import de.fuberlin.processing.who.cancer.data.structs.Struct_01_LifeExpectancyToHealthFinancing;
import de.fuberlin.processing.who.cancer.data.structs.Struct_02_HealthFinancingToCancerIncidence;
import de.fuberlin.processing.who.cancer.data.structs.Struct_03_LifeExpectancyToCancerIncidence;
import de.fuberlin.processing.who.cancer.data.structs.Struct_04_CancerIncidenceToCancerMortality;
import de.fuberlin.processing.who.cancer.data.structs.Struct_05_HealthFinancingToCancerMortality;
import de.fuberlin.processing.who.cancer.data.structs.StructLocationName;
import de.fuberlin.processing.who.cancer.data.structs.StructRegionName;
import de.fuberlin.processing.who.cancer.data.structs.StructSubregionDescription;
import de.fuberlin.processing.who.cancer.data.structs.Struct_06_LifeExpectancyToCancerMortality;
import de.fuberlin.processing.who.cancer.data.structs.Struct_07_CancerMortalityToAirPollution;
import de.fuberlin.processing.who.cancer.data.structs.Struct_08_CancerIncidenceToAirPollution;
import de.fuberlin.processing.who.cancer.data.structs.Struct_09_HealthFinancingToAirPollution;
import de.fuberlin.processing.who.cancer.data.structs.Struct_10_LifeExpectancyToAirPollution;
import de.fuberlin.processing.who.cancer.data.structs.Struct_11_CancerMortalityToUvRadiation;
import de.fuberlin.processing.who.cancer.data.structs.Struct_12_CancerIncidenceToUvRadiation;
import de.fuberlin.processing.who.cancer.data.structs.Struct_13_HealthFinancingToUvRadiation;
import de.fuberlin.processing.who.cancer.data.structs.Struct_14_LifeExpectancyToUvRadiation;
import de.fuberlin.processing.who.cancer.data.structs.Struct_15_CancerMortalityToAge;
import de.fuberlin.processing.who.cancer.data.structs.Struct_16_CancerIncidenceToAge;
import de.fuberlin.processing.who.cancer.utils.Notification;
import de.fuberlin.processing.who.cancer.utils.Test;
import de.fuberlin.processing.who.cancer.utils.Typecast;


/**
 * Implements every database sql-query
 * 
 * 
 */
public class DatabaseConnectorImpl implements DatabaseConnector {

	/*
	 * Die Lebenserwartung in Abhängigkeit zu den Finanzausgaben der WHO-Länder
	 * für das Gesundheitswesen in USD.
	 */
	private static final String SQL_SELECT_01_LIFE_EXPECTANCY_BY_HEALTH_FINANCING = "SELECT "
			+ "l.location_id, "
			+ "life_expectancy, "
			+ "total_expenditures_per_gdb "
			+ "FROM  who_health_financing hf "
			+ "INNER JOIN who_life_expectancy le ON hf.location_id = le.location_id "
			+ "INNER JOIN who_locations l ON le.location_id = l.location_id "
			+ "ORDER BY life_expectancy";

	private static final String SQL_SELECT_02_HEALTH_FINANCING_TO_CANCER_INCIDENCE = "SELECT "
			+ "l.location_id, "
			+ "total, crude, "
			+ "asr, "
			+ "total_expenditures_per_gdb "
			+ "FROM  who_health_financing hf INNER JOIN globocan_population p  ON p.location_id = hf.location_id "
			+ "INNER JOIN  globocan_cancer_incidence cm ON p.population_id = cm.population_id  "
			+ "INNER JOIN who_locations l ON hf.location_id = l.location_id "
			+ "ORDER BY total_expenditures_per_gdb ASC";

	private static final String SQL_SELECT_03_LIFE_EXPECTANCY_TO_CANCER_INCIDENCE = "SELECT "
			+ "l.location_id, "
			+ "total, crude, "
			+ "asr, "
			+ "life_expectancy "
			+ "FROM  who_life_expectancy le INNER JOIN globocan_population p  ON p.location_id = le.location_id "
			+ "INNER JOIN  globocan_cancer_incidence cm ON p.population_id = cm.population_id  "
			+ "INNER JOIN who_locations l ON le.location_id = l.location_id "
			+ "ORDER BY life_expectancy ASC";

	private static final String SQL_SELECT_04_CANCER_INCIDENCE_TO_CANCER_MORTALITY = "SELECT "
			+ "l.location_id, "
			+ "ci.total as incidence_total,  "
			+ "ci.crude as incidence_crude, "
			+ "ci.asr as incidence_asr,  "
			+ "cm.total as mortality_total,  "
			+ "cm.crude as mortality_crude, "
			+ "cm.asr as mortality_asr "
			+ "FROM globocan_cancer_incidence ci INNER JOIN globocan_cancer_mortality cm ON ci.population_id = cm.population_id "
			+ "INNER JOIN globocan_population p ON p.population_id = cm.population_id "
			+ "INNER JOIN who_locations l ON p.location_id = l.location_id "
			+ "ORDER BY l.location_id";

	private static final String SQL_SELECT_LOCATION_NAMES = "SELECT "
			+ "location_id, " + "location_name " + "FROM  who_locations "
			+ "ORDER BY location_name";

	private static final String SQL_SELECT_LOCATION_NAMES_BY_THERE_IDS = "SELECT "
			+ "location_id, "
			+ "location_name "
			+ "FROM  who_locations "
			+ "WHERE location_id IN (%s) " + "ORDER BY location_name";

	private static final String SQL_SELECT_REGION_NAMES = "SELECT "
			+ "region_id, " + "region_name " + "FROM  who_regions "
			+ "ORDER BY region_name";

	private static final String SQL_SELECT_SUBREGION_DESCRIPTIONS = "SELECT "
			+ "subregion_id, " + "subregion_description " + "FROM  who_subregions "
			+ "ORDER BY subregion_id";

	private static final String SQL_SELECT_05_CANCER_MORTALITY_TO_HEALTH_FINANCING = "SELECT "
			+ "l.location_id, "
			+ "total, crude, "
			+ "asr, "
			+ "total_expenditures_per_gdb "
			+ "FROM  who_health_financing hf INNER JOIN globocan_population p  ON p.location_id = hf.location_id "
			+ "INNER JOIN  globocan_cancer_mortality cm ON p.population_id = cm.population_id  "
			+ "INNER JOIN who_locations l ON hf.location_id = l.location_id "
			+ "ORDER BY total_expenditures_per_gdb ASC";

	private static final String SQL_SELECT_06_CANCER_MORTALITY_TO_LIFE_EXPECTANCY = "SELECT "
			+ "l.location_id, "
			+ "total, crude, "
			+ "asr, "
			+ "life_expectancy "
			+ "FROM  who_life_expectancy le INNER JOIN globocan_population p  ON p.location_id = le.location_id "
			+ "INNER JOIN  globocan_cancer_mortality cm ON p.population_id = cm.population_id  "
			+ "INNER JOIN who_locations l ON le.location_id = l.location_id "
			+ "ORDER BY life_expectancy ASC";

	private static final String SQL_SELECT_07_CANCER_MORTALITY_TO_AIR_POLLUTION = "SELECT "
			+ "l.location_id, "
			+ "total, crude, "
			+ "asr, "
			+ "air_pollution "
			+ "FROM  who_outdoor_air_pollution_exposure ap INNER JOIN globocan_population p  ON p.location_id = ap.location_id "
			+ "INNER JOIN  globocan_cancer_mortality cm ON p.population_id = cm.population_id  "
			+ "INNER JOIN who_locations l ON ap.location_id = l.location_id "
			+ "ORDER BY air_pollution ASC";

	private static final String SQL_SELECT_08_CANCER_INCIDENCE_TO_AIR_POLLUTION = "SELECT "
			+ "l.location_id, "
			+ "total, crude, "
			+ "asr, "
			+ "air_pollution "
			+ "FROM  who_outdoor_air_pollution_exposure ap INNER JOIN globocan_population p  ON p.location_id = ap.location_id "
			+ "INNER JOIN  globocan_cancer_incidence ci ON p.population_id = ci.population_id  "
			+ "INNER JOIN who_locations l ON ap.location_id = l.location_id "
			+ "ORDER BY air_pollution ASC";

	private static final String SQL_SELECT_09_HEALTH_FINANCING_TO_AIR_POLLUTION = "SELECT "
			+ "l.location_id, "
			+ "total_expenditures_per_gdb, "
			+ "air_pollution "
			+ "FROM  who_health_financing hf "
			+ "INNER JOIN who_outdoor_air_pollution_exposure ap  ON hf.location_id = ap.location_id "
			+ "INNER JOIN who_locations l ON ap.location_id = l.location_id "
			+ "ORDER BY total_expenditures_per_gdb";

	private static final String SQL_SELECT_10_LIFE_EXPECTANCY_TO_AIR_POLLUTION = "SELECT "
			+ "l.location_id, "
			+ "life_expectancy, "
			+ "air_pollution "
			+ "FROM  who_life_expectancy le "
			+ "INNER JOIN who_outdoor_air_pollution_exposure ap  ON le.location_id = ap.location_id "
			+ "INNER JOIN who_locations l ON ap.location_id = l.location_id "
			+ "ORDER BY life_expectancy";

	private static final String SQL_SELECT_11_CANCER_MORTALITY_TO_UV_RADIATION = "SELECT "
			+ "l.location_id, "
			+ "total, crude, "
			+ "asr, "
			+ "uv_radiation "
			+ "FROM  who_uv_radiation_exposure uv INNER JOIN globocan_population p  ON p.location_id = uv.location_id "
			+ "INNER JOIN  globocan_cancer_mortality cm ON p.population_id = cm.population_id  "
			+ "INNER JOIN who_locations l ON uv.location_id = l.location_id "
			+ "ORDER BY uv_radiation ASC";

	private static final String SQL_SELECT_12_CANCER_INCIDENCE_TO_UV_RADIATION = "SELECT "
			+ "l.location_id, "
			+ "total, crude, "
			+ "asr, "
			+ "uv_radiation "
			+ "FROM  who_uv_radiation_exposure uv INNER JOIN globocan_population p  ON p.location_id = uv.location_id "
			+ "INNER JOIN  globocan_cancer_incidence ci ON p.population_id = ci.population_id  "
			+ "INNER JOIN who_locations l ON uv.location_id = l.location_id "
			+ "ORDER BY uv_radiation ASC";

	private static final String SQL_SELECT_13_HEALTH_FINANCING_TO_UV_RADIATION = "SELECT "
			+ "l.location_id, "
			+ "total_expenditures_per_gdb, "
			+ "uv_radiation "
			+ "FROM  who_health_financing hf "
			+ "INNER JOIN who_uv_radiation_exposure uv  ON hf.location_id = uv.location_id "
			+ "INNER JOIN who_locations l ON uv.location_id = l.location_id "
			+ "ORDER BY total_expenditures_per_gdb";

	private static final String SQL_SELECT_14_LIFE_EXPECTANCY_TO_UV_RADIATION = "SELECT "
			+ "l.location_id, "
			+ "life_expectancy, "
			+ "uv_radiation "
			+ "FROM  who_life_expectancy le "
			+ "INNER JOIN who_uv_radiation_exposure uv  ON le.location_id = uv.location_id "
			+ "INNER JOIN who_locations l ON uv.location_id = l.location_id "
			+ "ORDER BY life_expectancy";

	private static final String SQL_SELECT_15_CANCER_MORTALITY_BY_AGE = "SELECT "
			+ "l.location_id, "
			+ "age_0_14, "
			+ "age_15_39, "
			+ "age_40_44, "
			+ "age_45_49, "
			+ "age_50_54, "
			+ "age_55_59, "
			+ "age_60_64, "
			+ "age_65_69, "
			+ "age_70_74, "
			+ "age_75plus "
			+ "FROM globocan_all_cancer_by_sex_and_age_as_rates gr INNER JOIN globocan_population p ON gr.population_id = p.population_id "
			+ "INNER JOIN who_locations l ON p.location_id = l.location_id "
			+ "WHERE cancer_name = \"All cancers excl. non-melanoma skin cancer\" "
			+ "AND type = \"mortality\" " + "AND sex=\"both\" "
			+ "ORDER BY l.location_id";

	private static final String SQL_SELECT_16_CANCER_INCIDENCE_BY_AGE = "SELECT "
			+ "l.location_id, "
			+ "age_0_14, "
			+ "age_15_39, "
			+ "age_40_44, "
			+ "age_45_49, "
			+ "age_50_54, "
			+ "age_55_59, "
			+ "age_60_64, "
			+ "age_65_69, "
			+ "age_70_74, "
			+ "age_75plus "
			+ "FROM globocan_all_cancer_by_sex_and_age_as_rates gr INNER JOIN globocan_population p ON gr.population_id = p.population_id "
			+ "INNER JOIN who_locations l ON p.location_id = l.location_id "
			+ "WHERE cancer_name = \"All cancers excl. non-melanoma skin cancer\" "
			+ "AND type = \"incidence\" " + "AND sex=\"both\" "
			+ "ORDER BY l.location_id";

	private static final String SQL_SELECT_LOCATIONS_BY_REGION_AND_SUBREGIONS = "SELECT "
			+ "l.location_id "
			+ "FROM  who_locations l INNER JOIN who_regions_to_locations r2l ON l.location_id = r2l.location_id "
			+ "WHERE "
			+ "r2l.region_id = %d "
			+ "AND "
			+ "r2l.subregion_id IN (%s) "
			+ "ORDER BY l.location_id";

	private static final String SQL_SELECT_LOCATIONS_BY_REGION = "SELECT "
			+ "l.location_id "
			+ "FROM  who_locations l INNER JOIN who_regions_to_locations r2l ON l.location_id = r2l.location_id "
			+ "WHERE " + "r2l.region_id = %d " + "ORDER BY l.location_id";

	private static final String SQL_SELECT_REGIONS_BY_LOCATIONS = "SELECT "
			+ "region_id " + "FROM  who_regions_to_locations " + "WHERE "
			+ "location_id IN (%s) " + "ORDER BY region_id";

	private static final String SQL_SELECT_SUBREGIONS_BY_LOCATIONS = "SELECT "
			+ "subregion_id " + "FROM  who_regions_to_locations " + "WHERE "
			+ "location_id IN (%s) " + "GROUP BY subregion_id "
			+ "ORDER BY region_id";

	private static final String SQL_SELECT_MAX_LIFE_EXPECTANCY = "SELECT "
			+ "MAX(life_expectancy) " + "FROM who_life_expectancy";

	private static final String SQL_SELECT_MAX_HEALTH_FINANCING = "SELECT "
			+ "MAX(total_expenditures_per_gdb) " + "FROM who_health_financing";

	private static final String SQL_SELECT_LIFE_EXPECTANCY_AND_HEALTH_FINANCING_AND_CANCER_DATA_FOR_A_LOCATION = "SELECT "
			+ "l.location_id, "
			+ "l.location_name, "
			+ "le.life_expectancy, "
			+ "hf.total_expenditures_per_gdb, "
			+ "i.population_id, "
			+ "i.total as incidence_total, "
			+ "i.crude as incidence_crude, "
			+ "i.asr as incidence_asr, "
			+ "m.total as mortality_total, "
			+ "m.crude as mortality_crude, "
			+ "m.asr as mortality_asr "
			+ "FROM `globocan_cancer_incidence` i "
			+ "INNER JOIN `globocan_cancer_mortality` m  ON m.population_id = i.population_id "
			+ "INNER JOIN globocan_population p ON i.population_id = p.population_id "
			+ "INNER JOIN who_locations l ON p.location_id = l.location_id "
			+ "INNER JOIN who_life_expectancy le ON le.location_id = l.location_id "
			+ "INNER JOIN who_health_financing hf ON l.location_id = hf.location_id "
			+ "WHERE l.location_id = %d";

	private static final String SQL_SELECT_OUTDOOR_AIR_POLLUTION_EXPOSURE_FOR_A_LOCATION = "SELECT "
			+ "location_id, "
			+ "air_pollution "
			+ "FROM who_outdoor_air_pollution_exposure " + "WHERE location_id = %d";

	private static final String SQL_SELECT_MAX_OUTDOOR_AIR_POLLUTION_EXPOSURE = "SELECT "
			+ "MAX( air_pollution) " + "FROM who_outdoor_air_pollution_exposure";

	private static final String SQL_SELECT_UV_RADIATION_EXPOSURE_FOR_A_LOCATION = "SELECT "
			+ "location_id, "
			+ "uv_radiation "
			+ "FROM who_uv_radiation_exposure "
			+ "WHERE location_id = %d";

	private static final String SQL_SELECT_MAX_UV_RADIATION_EXPOSURE = "SELECT "
			+ "MAX( uv_radiation) " + "FROM who_uv_radiation_exposure";

	private static final String SQL_SELECT_CANCER_INCIDENCE_AGE_SPREADING_FOR_A_LOCATION = "SELECT "
			+ "p.location_id, "
			+ "age_0_14 / total *100 as age_0_14, "
			+ "(age_15_39+age_40_44+age_45_49+age_50_54+age_55_59) / total *100 as age_15_59, "
			+ "(age_60_64+age_65_69+age_70_74+age_75plus) / total * 100 as age_60plus, "
			+ "total "
			+ "FROM globocan_population p INNER JOIN globocan_all_cancer_by_sex_and_age_as_numbers cn ON p.population_id = cn.population_id "
			+ "WHERE cn.sex=\"both\" "
			+ "AND type=\"incidence\" "
			+ "AND cancer_name=\"All cancers excl. non-melanoma skin cancer\" "
			+ "AND p.location_id = %s";

	private static final String SQL_SELECT_CANCER_MORTALITY_AGE_SPREADING_FOR_A_LOCATION = "SELECT "
			+ "p.location_id, "
			+ "age_0_14 / total *100 as age_0_14, "
			+ "(age_15_39+age_40_44+age_45_49+age_50_54+age_55_59) / total *100 as age_15_59, "
			+ "(age_60_64+age_65_69+age_70_74+age_75plus) / total * 100 as age_60plus, "
			+ "total "
			+ "FROM globocan_population p INNER JOIN globocan_all_cancer_by_sex_and_age_as_numbers cn ON p.population_id = cn.population_id "
			+ "WHERE cn.sex=\"both\" "
			+ "AND type=\"mortality\" "
			+ "AND cancer_name=\"All cancers excl. non-melanoma skin cancer\" "
			+ "AND p.location_id = %s";

	private static final String SQL_SELECT_CANCER_INCIDENCE_SEX_SPREADING_FOR_A_LOCATION = "SELECT "
			+ "_p.location_id, "
			+ "(SELECT total FROM globocan_population p INNER JOIN globocan_all_cancer_by_sex_and_age_as_numbers cn ON p.population_id = cn.population_id WHERE cn.sex=\"male\"  AND cn.type=\"incidence\"  AND cn.cancer_name=\"All cancers excl. non-melanoma skin cancer\" AND p.location_id = _p.location_id ORDER BY p.population_id LIMIT 1) / total *100 AS male_incidence_per_100,  "
			+ "(SELECT total FROM globocan_population p INNER JOIN globocan_all_cancer_by_sex_and_age_as_numbers cn ON p.population_id = cn.population_id WHERE cn.sex=\"female\"  AND cn.type=\"incidence\"  AND cn.cancer_name=\"All cancers excl. non-melanoma skin cancer\" AND p.location_id = _p.location_id ORDER BY p.population_id LIMIT 1) / total *100 AS female_incidence_per_100, "
			+ "cn.total AS total_incidence  "
			+ "FROM globocan_population _p INNER JOIN globocan_all_cancer_by_sex_and_age_as_numbers cn ON _p.population_id = cn.population_id "
			+ "WHERE cn.sex=\"both\"  "
			+ "AND type=\"incidence\"  "
			+ "AND cancer_name=\"All cancers excl. non-melanoma skin cancer\" "
			+ "AND _p.location_id = %d";

	private static final String SQL_SELECT_CANCER_MORTALITY_SEX_SPREADING_FOR_A_LOCATION = "SELECT "
			+ "_p.location_id, "
			+ "(SELECT total FROM globocan_population p INNER JOIN globocan_all_cancer_by_sex_and_age_as_numbers cn ON p.population_id = cn.population_id WHERE cn.sex=\"male\"  AND cn.type=\"mortality\"  AND cn.cancer_name=\"All cancers excl. non-melanoma skin cancer\" AND p.location_id = _p.location_id ORDER BY p.population_id LIMIT 1) / total *100 AS male_incidence_per_100,  "
			+ "(SELECT total FROM globocan_population p INNER JOIN globocan_all_cancer_by_sex_and_age_as_numbers cn ON p.population_id = cn.population_id WHERE cn.sex=\"female\"  AND cn.type=\"mortality\"  AND cn.cancer_name=\"All cancers excl. non-melanoma skin cancer\" AND p.location_id = _p.location_id ORDER BY p.population_id LIMIT 1) / total *100 AS female_incidence_per_100, "
			+ "cn.total AS total_incidence  "
			+ "FROM globocan_population _p INNER JOIN globocan_all_cancer_by_sex_and_age_as_numbers cn ON _p.population_id = cn.population_id "
			+ "WHERE cn.sex=\"both\"  "
			+ "AND type=\"mortality\"  "
			+ "AND cancer_name=\"All cancers excl. non-melanoma skin cancer\" "
			+ "AND _p.location_id = %d";

	private static final String SQL_SELECT_LOCATION_DATA_FOR_A_LOCATION = "SELECT "
			+ "l.location_id, "
			+ "l.location_name, "
			+ "p.population_id "
			+ "FROM  who_locations l "
			+ "INNER JOIN globocan_population p ON l.location_id = p.location_id "
			+ "WHERE l.location_id = %d";

	private static final String SQL_SELECT_CANCER_MORTALITY_FOR_LOCATIONS = "SELECT "
			+ "l.location_id, "
			+ "total, crude, "
			+ "asr "
			+ "FROM  globocan_population p "
			+ "INNER JOIN  globocan_cancer_mortality cm ON p.population_id = cm.population_id  "
			+ "INNER JOIN who_locations l ON p.location_id = l.location_id "
			+ "WHERE l.location_id IN (%s) " + "ORDER BY l.location_name ASC";

	private static final String SQL_SELECT_CANCER_INCIDENCE_FOR_LOCATIONS = "SELECT "
			+ "l.location_id, "
			+ "total, crude, "
			+ "asr "
			+ "FROM  globocan_population p "
			+ "INNER JOIN  globocan_cancer_incidence ci ON p.population_id = ci.population_id  "
			+ "INNER JOIN who_locations l ON p.location_id = l.location_id "
			+ "WHERE l.location_id IN (%s) " + "ORDER BY l.location_name ASC";

	private static final String SQL_SELECT_LIFE_EXPECTANCY_FOR_LOCATIONS = "SELECT "
			+ "l.location_id, "
			+ "le.life_expectancy "
			+ "FROM who_life_expectancy le INNER JOIN who_locations l ON le.location_id = l.location_id "
			+ "WHERE l.location_id IN (%s) " + "ORDER BY l.location_name ASC";

	private static final String SQL_SELECT_HEALTH_FINANCING_FOR_LOCATIONS = "SELECT "
			+ "l.location_id, "
			+ "total_expenditures_per_gdb "
			+ "FROM  who_health_financing hf "
			+ "INNER JOIN  who_locations l ON hf.location_id = l.location_id "
			+ "WHERE l.location_id IN (%s) " + "ORDER BY l.location_name ASC";

	private static final String SQL_SELECT_AIR_POLLUTION_FOR_LOCATIONS = "SELECT "
			+ "l.location_id, "
			+ "air_pollution "
			+ "FROM  who_outdoor_air_pollution_exposure ap INNER JOIN who_locations l ON ap.location_id = l.location_id "
			+ "WHERE l.location_id IN (%s) " + "ORDER BY l.location_name ASC";

	private static final String SQL_SELECT_UV_RADIATION_FOR_LOCATIONS = "SELECT "
			+ "l.location_id, "
			+ "uv_radiation "
			+ "FROM  who_uv_radiation_exposure uv INNER JOIN who_locations l ON uv.location_id = l.location_id "
			+ "WHERE l.location_id IN (%s) " + "ORDER BY l.location_name ASC";

	private static final String SQL_SELECT_CANCER_INCIDENCE_AGE_SPREADING_FOR_A_LOCATIONS = "SELECT "
			+ "p.location_id, "
			+ "age_0_14 / total *100 as age_0_14, "
			+ "(age_15_39+age_40_44+age_45_49+age_50_54+age_55_59) / total *100 as age_15_59, "
			+ "(age_60_64+age_65_69+age_70_74+age_75plus) / total * 100 as age_60plus, "
			+ "total "
			+ "FROM globocan_population p INNER JOIN globocan_all_cancer_by_sex_and_age_as_numbers cn ON p.population_id = cn.population_id "
			+ "WHERE cn.sex=\"both\" "
			+ "AND type=\"incidence\" "
			+ "AND cancer_name=\"All cancers excl. non-melanoma skin cancer\" "
			+ "AND p.location_id IN (%s)";

	private static final String SQL_SELECT_CANCER_MORTALITY_AGE_SPREADING_FOR_A_LOCATIONS = "SELECT "
			+ "p.location_id, "
			+ "age_0_14 / total *100 as age_0_14, "
			+ "(age_15_39+age_40_44+age_45_49+age_50_54+age_55_59) / total *100 as age_15_59, "
			+ "(age_60_64+age_65_69+age_70_74+age_75plus) / total * 100 as age_60plus, "
			+ "total "
			+ "FROM globocan_population p INNER JOIN globocan_all_cancer_by_sex_and_age_as_numbers cn ON p.population_id = cn.population_id "
			+ "WHERE cn.sex=\"both\" "
			+ "AND type=\"mortality\" "
			+ "AND cancer_name=\"All cancers excl. non-melanoma skin cancer\" "
			+ "AND p.location_id IN (%s)";

	

	static Connection db; // A connection to the database
	static DatabaseMetaData dbmd; // This is basically info the driver delivers


	public DatabaseConnectorImpl()
			throws DBException {
		super();
		establishConnection();
	}


	


	private void establishConnection() throws DBException {
		// connect to the db
		try {
			// load configuration
			DatabaseConfig.loadConfig();
			Class.forName( DatabaseConfig.dbDriver); // load the driver

			db = DriverManager.getConnection( DatabaseConfig.dbProtocol + "://" + DatabaseConfig.dbHost + ":"
					+ DatabaseConfig.dbPort + "/" + DatabaseConfig.dbDatabase, DatabaseConfig.dbUser, DatabaseConfig.dbPassword);
			dbmd = db.getMetaData(); // get MetaData to confirm connection
			System.out.println( "Connection to " + dbmd.getDatabaseProductName()
					+ " " + dbmd.getDatabaseProductVersion() + " successful.\n");

		} catch ( Exception e) {
			Notification.printDebugException( e);
			throw new DBException( "Cannot establish a database connection!\n"
					+ e.getMessage());
		}
	}


	private ResultSet getResultForQuery( String query) throws DBException {
		ResultSet rs = null;
		try {
			PreparedStatement sql = db.prepareStatement( query);
			rs = sql.executeQuery();
		} catch ( Exception e) {
			Notification.printDebugException( e);
			throw new DBException( "Error occur while execute query.");
		}
		return rs;
	}


	/**
	 * Lebenserwartung zu Gesundheitsausgaben
	 */
	public Collection<Struct_01_LifeExpectancyToHealthFinancing> getLifeExpectancyByHealthFinancing() {
		Collection<Struct_01_LifeExpectancyToHealthFinancing> c = new Vector<Struct_01_LifeExpectancyToHealthFinancing>();
		try {
			ResultSet rs = getResultForQuery( SQL_SELECT_01_LIFE_EXPECTANCY_BY_HEALTH_FINANCING);
			try {
				while ( rs.next()) {
					c.add( new Struct_01_LifeExpectancyToHealthFinancing( rs.getInt( 1),
							rs.getInt( 2), rs.getFloat( 3)));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	@Override
	public Collection<Struct_02_HealthFinancingToCancerIncidence> getHealthFinancingToCancerIncidence() {
		Collection<Struct_02_HealthFinancingToCancerIncidence> c = new Vector<Struct_02_HealthFinancingToCancerIncidence>();
		try {
			ResultSet rs = getResultForQuery( SQL_SELECT_02_HEALTH_FINANCING_TO_CANCER_INCIDENCE);
			try {
				while ( rs.next()) {
					c.add( new Struct_02_HealthFinancingToCancerIncidence( rs.getInt( 1),
							rs.getInt( 2), rs.getFloat( 3), rs.getFloat( 4), rs.getFloat( 5)));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	@Override
	public Collection<Struct_03_LifeExpectancyToCancerIncidence> getLifeExpectancyToCancerIncidence() {
		Collection<Struct_03_LifeExpectancyToCancerIncidence> c = new Vector<Struct_03_LifeExpectancyToCancerIncidence>();
		try {
			ResultSet rs = getResultForQuery( SQL_SELECT_03_LIFE_EXPECTANCY_TO_CANCER_INCIDENCE);
			try {
				while ( rs.next()) {
					c.add( new Struct_03_LifeExpectancyToCancerIncidence( rs.getInt( 1),
							rs.getInt( 2), rs.getFloat( 3), rs.getFloat( 4), rs.getInt( 5)));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	@Override
	public Collection<Struct_04_CancerIncidenceToCancerMortality> getCancerIncidenceToCancerMortality() {
		Collection<Struct_04_CancerIncidenceToCancerMortality> c = new Vector<Struct_04_CancerIncidenceToCancerMortality>();
		try {
			ResultSet rs = getResultForQuery( SQL_SELECT_04_CANCER_INCIDENCE_TO_CANCER_MORTALITY);
			try {
				while ( rs.next()) {
					c.add( new Struct_04_CancerIncidenceToCancerMortality( rs.getInt( 1),
							rs.getInt( 2), rs.getFloat( 3), rs.getFloat( 4), rs.getInt( 5),
							rs.getFloat( 6), rs.getFloat( 7)));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	/**
	 * Krebstote (bezgl. 1k Population) zu Gesundheitsausgaben
	 */
	public Collection<Struct_05_HealthFinancingToCancerMortality> getCancerMortalityToHealthFinancing() {
		Collection<Struct_05_HealthFinancingToCancerMortality> c = new Vector<Struct_05_HealthFinancingToCancerMortality>();
		try {
			ResultSet rs = getResultForQuery( SQL_SELECT_05_CANCER_MORTALITY_TO_HEALTH_FINANCING);
			try {
				while ( rs.next()) {
					c.add( new Struct_05_HealthFinancingToCancerMortality( rs.getInt( 1),
							rs.getInt( 2), rs.getFloat( 3), rs.getFloat( 4), rs.getFloat( 5)));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	/**
	 * Krebstote (bezgl. 1k Population) zu Lebenserwartung
	 */
	public Collection<Struct_06_LifeExpectancyToCancerMortality> getCancerMortalityToLifeExpectancy() {
		Collection<Struct_06_LifeExpectancyToCancerMortality> c = new Vector<Struct_06_LifeExpectancyToCancerMortality>();
		try {
			ResultSet rs = getResultForQuery( SQL_SELECT_06_CANCER_MORTALITY_TO_LIFE_EXPECTANCY);
			try {
				while ( rs.next()) {
					c.add( new Struct_06_LifeExpectancyToCancerMortality( rs.getInt( 1),
							rs.getInt( 2), rs.getFloat( 3), rs.getInt( 4), rs.getInt( 5)));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	@Override
	public Collection<Struct_07_CancerMortalityToAirPollution> getCancerMortalityToAirPollution() {
		Collection<Struct_07_CancerMortalityToAirPollution> c = new Vector<Struct_07_CancerMortalityToAirPollution>();
		try {
			ResultSet rs = getResultForQuery( SQL_SELECT_07_CANCER_MORTALITY_TO_AIR_POLLUTION);
			try {
				while ( rs.next()) {
					c.add( new Struct_07_CancerMortalityToAirPollution( rs.getInt( 1), rs
							.getInt( 2), rs.getFloat( 3), rs.getInt( 4), rs.getInt( 5)));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	@Override
	public Collection<Struct_08_CancerIncidenceToAirPollution> getCancerIncidenceToAirPollution() {
		Collection<Struct_08_CancerIncidenceToAirPollution> c = new Vector<Struct_08_CancerIncidenceToAirPollution>();
		try {
			ResultSet rs = getResultForQuery( SQL_SELECT_08_CANCER_INCIDENCE_TO_AIR_POLLUTION);
			try {
				while ( rs.next()) {
					c.add( new Struct_08_CancerIncidenceToAirPollution( rs.getInt( 1), rs
							.getInt( 2), rs.getFloat( 3), rs.getInt( 4), rs.getInt( 5)));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	@Override
	public Collection<Struct_09_HealthFinancingToAirPollution> getHealthFinancingToAirPollution() {
		Collection<Struct_09_HealthFinancingToAirPollution> c = new Vector<Struct_09_HealthFinancingToAirPollution>();
		try {
			ResultSet rs = getResultForQuery( SQL_SELECT_09_HEALTH_FINANCING_TO_AIR_POLLUTION);
			try {
				while ( rs.next()) {
					c.add( new Struct_09_HealthFinancingToAirPollution( rs.getInt( 1), rs
							.getFloat( 2), rs.getInt( 3)));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	@Override
	public Collection<Struct_10_LifeExpectancyToAirPollution> getLifeExpectancyToAirPollution() {
		Collection<Struct_10_LifeExpectancyToAirPollution> c = new Vector<Struct_10_LifeExpectancyToAirPollution>();
		try {
			ResultSet rs = getResultForQuery( SQL_SELECT_10_LIFE_EXPECTANCY_TO_AIR_POLLUTION);
			try {
				while ( rs.next()) {
					c.add( new Struct_10_LifeExpectancyToAirPollution( rs.getInt( 1), rs
							.getInt( 2), rs.getInt( 3)));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	@Override
	public Collection<Struct_11_CancerMortalityToUvRadiation> getCancerMortalityToUvRadiation() {
		Collection<Struct_11_CancerMortalityToUvRadiation> c = new Vector<Struct_11_CancerMortalityToUvRadiation>();
		try {
			ResultSet rs = getResultForQuery( SQL_SELECT_11_CANCER_MORTALITY_TO_UV_RADIATION);
			try {
				while ( rs.next()) {
					c.add( new Struct_11_CancerMortalityToUvRadiation( rs.getInt( 1), rs
							.getInt( 2), rs.getFloat( 3), rs.getFloat( 4), rs.getInt( 5)));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	@Override
	public Collection<Struct_12_CancerIncidenceToUvRadiation> getCancerIncidenceToUvRadiation() {
		Collection<Struct_12_CancerIncidenceToUvRadiation> c = new Vector<Struct_12_CancerIncidenceToUvRadiation>();
		try {
			ResultSet rs = getResultForQuery( SQL_SELECT_12_CANCER_INCIDENCE_TO_UV_RADIATION);
			try {
				while ( rs.next()) {
					c.add( new Struct_12_CancerIncidenceToUvRadiation( rs.getInt( 1), rs
							.getInt( 2), rs.getFloat( 3), rs.getFloat( 4), rs.getInt( 5)));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	@Override
	public Collection<Struct_13_HealthFinancingToUvRadiation> getHealthFinancingToUvRadiation() {
		Collection<Struct_13_HealthFinancingToUvRadiation> c = new Vector<Struct_13_HealthFinancingToUvRadiation>();
		try {
			ResultSet rs = getResultForQuery( SQL_SELECT_13_HEALTH_FINANCING_TO_UV_RADIATION);
			try {
				while ( rs.next()) {
					c.add( new Struct_13_HealthFinancingToUvRadiation( rs.getInt( 1), rs
							.getFloat( 2), rs.getInt( 3)));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	@Override
	public Collection<Struct_14_LifeExpectancyToUvRadiation> getLifeExpectancyToUvRadiation() {
		Collection<Struct_14_LifeExpectancyToUvRadiation> c = new Vector<Struct_14_LifeExpectancyToUvRadiation>();
		try {
			ResultSet rs = getResultForQuery( SQL_SELECT_14_LIFE_EXPECTANCY_TO_UV_RADIATION);
			try {
				while ( rs.next()) {
					c.add( new Struct_14_LifeExpectancyToUvRadiation( rs.getInt( 1), rs
							.getInt( 2), rs.getInt( 3)));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	@Override
	public Collection<Struct_15_CancerMortalityToAge> getCancerMortalityToAge() {
		Collection<Struct_15_CancerMortalityToAge> c = new Vector<Struct_15_CancerMortalityToAge>();
		try {
			ResultSet rs = getResultForQuery( SQL_SELECT_15_CANCER_MORTALITY_BY_AGE);
			try {
				while ( rs.next()) {
					c.add( new Struct_15_CancerMortalityToAge( rs.getInt( 1), rs
							.getFloat( 2), rs.getFloat( 3), rs.getFloat( 4), rs.getFloat( 5),
							rs.getFloat( 6), rs.getFloat( 7), rs.getFloat( 8), rs
									.getFloat( 9), rs.getFloat( 10), rs.getFloat( 11)));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	@Override
	public Collection<Struct_16_CancerIncidenceToAge> getCancerIncidenceToAge() {
		Collection<Struct_16_CancerIncidenceToAge> c = new Vector<Struct_16_CancerIncidenceToAge>();
		try {
			ResultSet rs = getResultForQuery( SQL_SELECT_16_CANCER_INCIDENCE_BY_AGE);
			try {
				while ( rs.next()) {
					c.add( new Struct_16_CancerIncidenceToAge( rs.getInt( 1), rs
							.getFloat( 2), rs.getFloat( 3), rs.getFloat( 4), rs.getFloat( 5),
							rs.getFloat( 6), rs.getFloat( 7), rs.getFloat( 8), rs
									.getFloat( 9), rs.getFloat( 10), rs.getFloat( 11)));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	/**
	 * Location Namen ( Namen der Länder)
	 */
	public Collection<StructLocationName> getLocationNames() {
		Collection<StructLocationName> c = new Vector<StructLocationName>();
		try {
			ResultSet rs = getResultForQuery( SQL_SELECT_LOCATION_NAMES);
			try {
				while ( rs.next()) {
					c.add( new StructLocationName( rs.getInt( 1), rs.getString( 2)));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	/**
	 * Liefert die Location Namen ( Namen der Länder) einer Selektion
	 */
	public Collection<StructLocationName> getLocationNamesByThereIDs(
			Collection<Integer> selectionOfLocationIDs) {
		Collection<StructLocationName> c = new Vector<StructLocationName>();
		try {
			String csvIdString = Typecast
					.convertIntegersToCommaSeparatedString( selectionOfLocationIDs);
			String preparedSQLStatement = String.format(
					SQL_SELECT_LOCATION_NAMES_BY_THERE_IDS, csvIdString);
			ResultSet rs = getResultForQuery( preparedSQLStatement);
			try {
				while ( rs.next()) {
					c.add( new StructLocationName( rs.getInt( 1), rs.getString( 2)));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	/**
	 * WHO Regionen-Namen
	 */
	@Override
	public Collection<StructRegionName> getRegionNames() {
		Collection<StructRegionName> c = new Vector<StructRegionName>();
		try {
			ResultSet rs = getResultForQuery( SQL_SELECT_REGION_NAMES);
			try {
				while ( rs.next()) {
					c.add( new StructRegionName( rs.getInt( 1), rs.getString( 2)));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	/**
	 * WHO Sub-Regionen
	 */
	@Override
	public Collection<StructSubregionDescription> getSubregionDescriptions() {
		Collection<StructSubregionDescription> c = new Vector<StructSubregionDescription>();
		try {
			ResultSet rs = getResultForQuery( SQL_SELECT_SUBREGION_DESCRIPTIONS);
			try {
				while ( rs.next()) {
					c.add( new StructSubregionDescription( rs.getString( 1).charAt( 0),
							rs.getString( 2)));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	private Collection<Integer> queryForIntegers( String query) {
		Collection<Integer> c = new Vector<Integer>();
		try {
			ResultSet rs = getResultForQuery( query);
			try {
				while ( rs.next()) {
					c.add( rs.getInt( 1));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	private Collection<Character> queryForCharacters( String query) {
		Collection<Character> c = new Vector<Character>();
		try {
			ResultSet rs = getResultForQuery( query);
			try {
				while ( rs.next()) {
					c.add( rs.getString( 1).charAt( 0));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	private Integer queryForAnInteger( String query) {
		Integer result = new Integer( 0);
		try {
			ResultSet rs = getResultForQuery( query);
			try {
				if ( rs.next()) {
					result = rs.getInt( 1);
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return result;
	}


	private Float queryForAnFloat( String query) {
		Float result = new Float( 0);
		try {
			ResultSet rs = getResultForQuery( query);
			try {
				if ( rs.next()) {
					result = rs.getFloat( 1);
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return result;
	}


	private Collection<Integer> getLocationsBy( String query) {
		return queryForIntegers( query);
	}


	private Collection<Integer> getRegionsBy( String query) {
		return queryForIntegers( query);
	}


	private Collection<Character> getSubregionsBy( String query) {
		return queryForCharacters( query);
	}


	@Override
	public Collection<Integer> getLocationsOfRegion( int regionId) {
		String preparedSQLStatement = String.format(
				SQL_SELECT_LOCATIONS_BY_REGION, regionId);
		return getLocationsBy( preparedSQLStatement);
	}


	@Override
	public Collection<Integer> getLocationsOfRegionFilteredBySubregions(
			int regionId, Collection<Character> selectedSubregions) {
		String csvSubregions = Typecast
				.convertCharactersToCommaSeparatedString( selectedSubregions);
		String preparedSQLStatement = String.format(
				SQL_SELECT_LOCATIONS_BY_REGION_AND_SUBREGIONS, regionId, csvSubregions);
		if ( csvSubregions.isEmpty())
			return null;
		else
			return getLocationsBy( preparedSQLStatement);
	}


	@Override
	public Collection<Integer> getRegionsOfLocations(
			Collection<Integer> locations) {
		String csvSubregions = Typecast
				.convertIntegersToCommaSeparatedString( locations);
		String preparedSQLStatement = String.format(
				SQL_SELECT_REGIONS_BY_LOCATIONS, csvSubregions);
		if ( csvSubregions.isEmpty())
			return null;
		else
			return getRegionsBy( preparedSQLStatement);
	}


	@Override
	public Collection<Character> getSubregionsOfLocations(
			Collection<Integer> locations) {
		String csvSubregions = Typecast
				.convertIntegersToCommaSeparatedString( locations);
		String preparedSQLStatement = String.format(
				SQL_SELECT_SUBREGIONS_BY_LOCATIONS, csvSubregions);
		if ( csvSubregions.isEmpty())
			return null;
		else
			return getSubregionsBy( preparedSQLStatement);
	}


	@Override
	public Integer getMaxLifeExpenctancy() {
		return queryForAnInteger( SQL_SELECT_MAX_LIFE_EXPECTANCY);
	}


	@Override
	public Float getMaxHealthFinancing() {
		return queryForAnFloat( SQL_SELECT_MAX_HEALTH_FINANCING);
	}


	/**
	 * Liefert die Location Id, den Location Namen, Daten über Health Financing
	 * und Life Expectancy sowie relevante Krebsdaten.
	 * 
	 * @see HealthGraph
	 */
	public StructLifeExpectancyAndHealthFinancingAndCancerDataForLocation getLifeExpectancyAndHealthFinancingAndCancerDataForLocation(
			int locationId) {
		StructLifeExpectancyAndHealthFinancingAndCancerDataForLocation st = null;
		try {
			String preparedSQLStatement = String
					.format(
							SQL_SELECT_LIFE_EXPECTANCY_AND_HEALTH_FINANCING_AND_CANCER_DATA_FOR_A_LOCATION,
							locationId);
			ResultSet rs = getResultForQuery( preparedSQLStatement);
			try {
				if ( rs.next()) {
					st = new StructLifeExpectancyAndHealthFinancingAndCancerDataForLocation(
							rs.getInt( 1), rs.getString( 2), rs.getInt( 3), rs.getFloat( 4),
							rs.getInt( 5), rs.getInt( 6), rs.getFloat( 7), rs.getFloat( 8),
							rs.getInt( 9), rs.getFloat( 10), rs.getFloat( 11));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return st;
	}


	@Override
	public StructAirPollutionExposureOfLocation getAirPollutionExposureOfLocation(
			int locationId) {
		StructAirPollutionExposureOfLocation st = null;
		try {
			String preparedSQLStatement = String.format(
					SQL_SELECT_OUTDOOR_AIR_POLLUTION_EXPOSURE_FOR_A_LOCATION, locationId);
			ResultSet rs = getResultForQuery( preparedSQLStatement);
			try {
				if ( rs.next()) {
					st = new StructAirPollutionExposureOfLocation( rs.getInt( 1),
							rs.getInt( 2));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return st;
	}


	@Override
	public Integer getMaxAirPollutionExposure() {
		return queryForAnInteger( SQL_SELECT_MAX_OUTDOOR_AIR_POLLUTION_EXPOSURE);
	}


	@Override
	public StructUVRadiationExposureOfLocation getUVRadiationExposureOfLocation(
			int locationId) {
		StructUVRadiationExposureOfLocation st = null;
		try {
			String preparedSQLStatement = String.format(
					SQL_SELECT_UV_RADIATION_EXPOSURE_FOR_A_LOCATION, locationId);
			ResultSet rs = getResultForQuery( preparedSQLStatement);
			try {
				if ( rs.next()) {
					st = new StructUVRadiationExposureOfLocation( rs.getInt( 1),
							rs.getInt( 2));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return st;
	}


	@Override
	public Integer getMaxUVRadiationExposure() {
		return queryForAnInteger( SQL_SELECT_MAX_UV_RADIATION_EXPOSURE);
	}


	private StructCancerAgeSpreadingOfLocation getCancerAgeSpreatingBy(
			String query) {
		StructCancerAgeSpreadingOfLocation st = null;
		try {
			ResultSet rs = getResultForQuery( query);
			try {
				if ( rs.next()) {
					st = new StructCancerAgeSpreadingOfLocation( rs.getInt( 1),
							rs.getFloat( 2), rs.getFloat( 3), rs.getFloat( 4), rs.getInt( 5));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return st;
	}


	@Override
	public StructCancerAgeSpreadingOfLocation getCancerIncidenceAgeSpreatingOfLocation(
			int locationId) {
		String preparedSQLStatement = String.format(
				SQL_SELECT_CANCER_INCIDENCE_AGE_SPREADING_FOR_A_LOCATION, locationId);
		return getCancerAgeSpreatingBy( preparedSQLStatement);
	}


	@Override
	public StructCancerAgeSpreadingOfLocation getCancerMortalityAgeSpreatingOfLocation(
			int locationId) {
		String preparedSQLStatement = String.format(
				SQL_SELECT_CANCER_MORTALITY_AGE_SPREADING_FOR_A_LOCATION, locationId);
		return getCancerAgeSpreatingBy( preparedSQLStatement);
	}


	private StructCancerSexSpreadingOfLocation getCancerSexSpreatingBy(
			String query) {
		StructCancerSexSpreadingOfLocation st = null;
		try {
			ResultSet rs = getResultForQuery( query);
			try {
				if ( rs.next()) {
					st = new StructCancerSexSpreadingOfLocation( rs.getInt( 1),
							rs.getFloat( 2), rs.getFloat( 3), rs.getInt( 4));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return st;
	}


	@Override
	public StructCancerSexSpreadingOfLocation getCancerIncidenceSexSpreatingOfLocation(
			int locationId) {
		String preparedSQLStatement = String.format(
				SQL_SELECT_CANCER_INCIDENCE_SEX_SPREADING_FOR_A_LOCATION, locationId);
		return getCancerSexSpreatingBy( preparedSQLStatement);
	}


	@Override
	public StructCancerSexSpreadingOfLocation getCancerMortalitySexSpreatingOfLocation(
			int locationId) {
		String preparedSQLStatement = String.format(
				SQL_SELECT_CANCER_MORTALITY_SEX_SPREADING_FOR_A_LOCATION, locationId);
		return getCancerSexSpreatingBy( preparedSQLStatement);
	}


	@Override
	public StructLocationData getLocationDataByLocationId( int locationId) {
		StructLocationData st = null;
		try {
			String preparedSQLStatement = String.format(
					SQL_SELECT_LOCATION_DATA_FOR_A_LOCATION, locationId);
			ResultSet rs = getResultForQuery( preparedSQLStatement);
			try {
				if ( rs.next()) {
					st = new StructLocationData( rs.getInt( 1), rs.getString( 2),
							rs.getInt( 3));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return st;
	}


	public void disconnect() {
		try {
			db.close();
		} catch ( SQLException e) {
			e.printStackTrace();
		}
	}


	@Override
	public Collection<StructCancerMortalityOfLocation> getCancerMortalityOfLocations(
			Collection<Integer> locations) {
		Collection<StructCancerMortalityOfLocation> c = new Vector<StructCancerMortalityOfLocation>();
		if ( Test.isUnassigned( locations) || locations.size() == 0)
			return c;
		try {
			String csvIdString = Typecast
					.convertIntegersToCommaSeparatedString( locations);
			String preparedSQLStatement = String.format(
					SQL_SELECT_CANCER_MORTALITY_FOR_LOCATIONS, csvIdString);
			ResultSet rs = getResultForQuery( preparedSQLStatement);
			try {
				while ( rs.next()) {
					c.add( new StructCancerMortalityOfLocation( rs.getInt( 1), rs
							.getInt( 2), rs.getFloat( 3), rs.getFloat( 4)));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	@Override
	public Collection<StructCancerIncidenceOfLocation> getCancerIncidenceOfLocations(
			Collection<Integer> locations) {
		Collection<StructCancerIncidenceOfLocation> c = new Vector<StructCancerIncidenceOfLocation>();
		if ( Test.isUnassigned( locations) || locations.size() == 0)
			return c;
		try {
			String csvIdString = Typecast
					.convertIntegersToCommaSeparatedString( locations);
			String preparedSQLStatement = String.format(
					SQL_SELECT_CANCER_INCIDENCE_FOR_LOCATIONS, csvIdString);
			ResultSet rs = getResultForQuery( preparedSQLStatement);
			try {
				while ( rs.next()) {
					c.add( new StructCancerIncidenceOfLocation( rs.getInt( 1), rs
							.getInt( 2), rs.getFloat( 3), rs.getFloat( 4)));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	@Override
	public Collection<StructLifeExpectancyOfLocation> getLifeExpectancyOfLocations(
			Collection<Integer> locations) {
		Collection<StructLifeExpectancyOfLocation> c = new Vector<StructLifeExpectancyOfLocation>();
		if ( Test.isUnassigned( locations) || locations.size() == 0)
			return c;
		try {
			String csvIdString = Typecast
					.convertIntegersToCommaSeparatedString( locations);
			String preparedSQLStatement = String.format(
					SQL_SELECT_LIFE_EXPECTANCY_FOR_LOCATIONS, csvIdString);
			ResultSet rs = getResultForQuery( preparedSQLStatement);
			try {
				while ( rs.next()) {
					c.add( new StructLifeExpectancyOfLocation( rs.getInt( 1), rs
							.getInt( 2)));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	@Override
	public Collection<StructHealthFinancingOfLocation> getHealthFinancingOfLocations(
			Collection<Integer> locations) {
		Collection<StructHealthFinancingOfLocation> c = new Vector<StructHealthFinancingOfLocation>();
		if ( Test.isUnassigned( locations) || locations.size() == 0)
			return c;
		try {
			String csvIdString = Typecast
					.convertIntegersToCommaSeparatedString( locations);
			String preparedSQLStatement = String.format(
					SQL_SELECT_HEALTH_FINANCING_FOR_LOCATIONS, csvIdString);
			ResultSet rs = getResultForQuery( preparedSQLStatement);
			try {
				while ( rs.next()) {
					c.add( new StructHealthFinancingOfLocation( rs.getInt( 1), rs
							.getFloat( 2)));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	@Override
	public Collection<StructCancerCauseAirPollutionOfLocation> getCancerCauseAirPollutionOfLocations(
			Collection<Integer> locations) {
		Collection<StructCancerCauseAirPollutionOfLocation> c = new Vector<StructCancerCauseAirPollutionOfLocation>();
		if ( Test.isUnassigned( locations) || locations.size() == 0)
			return c;
		try {
			String csvIdString = Typecast
					.convertIntegersToCommaSeparatedString( locations);
			String preparedSQLStatement = String.format(
					SQL_SELECT_AIR_POLLUTION_FOR_LOCATIONS, csvIdString);
			ResultSet rs = getResultForQuery( preparedSQLStatement);
			try {
				while ( rs.next()) {
					c.add( new StructCancerCauseAirPollutionOfLocation( rs.getInt( 1), rs
							.getInt( 2)));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	@Override
	public Collection<StructCancerCauseUvRadiationOfLocation> getCancerCauseUvRadiationOfLocations(
			Collection<Integer> locations) {
		Collection<StructCancerCauseUvRadiationOfLocation> c = new Vector<StructCancerCauseUvRadiationOfLocation>();
		if ( Test.isUnassigned( locations) || locations.size() == 0)
			return c;
		try {
			String csvIdString = Typecast
					.convertIntegersToCommaSeparatedString( locations);
			String preparedSQLStatement = String.format(
					SQL_SELECT_UV_RADIATION_FOR_LOCATIONS, csvIdString);
			ResultSet rs = getResultForQuery( preparedSQLStatement);
			try {
				while ( rs.next()) {
					c.add( new StructCancerCauseUvRadiationOfLocation( rs.getInt( 1), rs
							.getInt( 2)));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	@Override
	public Collection<StructCancerAgeSpreadingOfLocation> getCancerIncidenceAgeSpreatingOfLocations(
			Collection<Integer> locations) {
		Collection<StructCancerAgeSpreadingOfLocation> c = new Vector<StructCancerAgeSpreadingOfLocation>();
		if ( Test.isUnassigned( locations) || locations.size() == 0)
			return c;
		try {
			String csvIdString = Typecast
					.convertIntegersToCommaSeparatedString( locations);
			String preparedSQLStatement = String.format(
					SQL_SELECT_CANCER_INCIDENCE_AGE_SPREADING_FOR_A_LOCATIONS,
					csvIdString);
			ResultSet rs = getResultForQuery( preparedSQLStatement);
			try {
				while ( rs.next()) {
					c.add( new StructCancerAgeSpreadingOfLocation( rs.getInt( 1), rs
							.getFloat( 2), rs.getFloat( 3), rs.getFloat( 4), rs.getInt( 5)));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	@Override
	public Collection<StructCancerAgeSpreadingOfLocation> getCancerMortalityAgeSpreatingOfLocations(
			Collection<Integer> locations) {
		Collection<StructCancerAgeSpreadingOfLocation> c = new Vector<StructCancerAgeSpreadingOfLocation>();
		if ( Test.isUnassigned( locations) || locations.size() == 0)
			return c;
		try {
			String csvIdString = Typecast
					.convertIntegersToCommaSeparatedString( locations);
			String preparedSQLStatement = String.format(
					SQL_SELECT_CANCER_MORTALITY_AGE_SPREADING_FOR_A_LOCATIONS,
					csvIdString);
			ResultSet rs = getResultForQuery( preparedSQLStatement);
			try {
				while ( rs.next()) {
					c.add( new StructCancerAgeSpreadingOfLocation( rs.getInt( 1), rs
							.getFloat( 2), rs.getFloat( 3), rs.getFloat( 4), rs.getInt( 5)));
				}
			} finally {
				if ( rs != null)
					try {
						rs.close();
					} catch ( SQLException e) {
						Notification.printDebugException( e);
					}
			}
		} catch ( Exception e) {
			Notification.printErrorMessage( "DB-ERROR: " + e.getMessage());
			return null;
		}
		return c;
	}


	protected void finalize() throws Throwable {
		try {
			disconnect(); // close open connection
		} finally {
			super.finalize();
		}
	}

}
